#!/usr/bin/env python3
"""
客户管理表结构更新脚本
更新customer表结构以支持新的字段需求
"""

import sys
import os
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app import create_app, db
from sqlalchemy import text

def update_customer_table_structure():
    """更新客户表结构"""
    app = create_app()
    with app.app_context():
        print("开始更新客户表结构...")
        
        try:
            # 检查表是否存在
            result = db.session.execute(text(
                "SELECT name FROM sqlite_master WHERE type='table' AND name='customer'"
            )).fetchone()
            
            if not result:
                print("错误：customer表不存在！")
                return False
            
            # 获取当前表结构
            columns = db.session.execute(text("PRAGMA table_info(customer)")).fetchall()
            column_names = [col[1] for col in columns]
            print(f"当前表字段: {column_names}")
            
            # 添加新字段
            new_columns = [
                ("category", "VARCHAR(50) NOT NULL DEFAULT '普通客户'"),
                ("category_other", "VARCHAR(100)"),
                ("location", "VARCHAR(100) NOT NULL DEFAULT '华东'"),
                ("source_other", "VARCHAR(100)"),
                ("province_id", "INTEGER"),
                ("city_id", "INTEGER"),
                ("district_id", "INTEGER")
            ]
            
            for col_name, col_def in new_columns:
                if col_name not in column_names:
                    print(f"添加字段: {col_name}")
                    db.session.execute(text(f"ALTER TABLE customer ADD COLUMN {col_name} {col_def}"))
                else:
                    print(f"字段 {col_name} 已存在，跳过")
            
            # 注意：SQLite不支持直接删除列，所以我们需要创建一个新表然后复制数据
            # 这里我们只是标记哪些字段将被忽略，而不是实际删除它们
            ignored_columns = [
                'industry', 'scale', 'type', 'status', 'phone', 'email', 
                'website', 'remarks', 'estimated_amount', 'estimated_close_date'
            ]
            
            print("\n以下字段将被保留但不再使用:")
            for col in ignored_columns:
                if col in column_names:
                    print(f"  - {col}")
            
            # 提交更改
            db.session.commit()
            print("\n客户表结构更新完成！")
            
            # 验证更新结果
            print("\n验证更新结果...")
            updated_columns = db.session.execute(text("PRAGMA table_info(customer)")).fetchall()
            updated_column_names = [col[1] for col in updated_columns]
            print(f"更新后表字段: {updated_column_names}")
            
            return True
            
        except Exception as e:
            db.session.rollback()
            print(f"更新失败: {str(e)}")
            return False

if __name__ == '__main__':
    success = update_customer_table_structure()
    if success:
        print("\n客户表结构更新成功完成！")
        print("注意：由于SQLite限制，旧字段未被物理删除，但已不再使用。")
    else:
        print("\n客户表结构更新失败，请检查错误信息！")
        sys.exit(1)